<?php

/**
 * @author
 * @copyright 2011
 */
include_once('class.dbconnect.php');
include('../../default.config.php');
class lsubirds{

	protected $db;
	function __construct() {
		$this->db = new DbConnect(DB_SERVER, DB_USER, DB_PASSWORD,DB_NAME);
	}

	function saveData($vernacularName, $state, $locality, $description, $stage, $imageId, $county) {

	 $this->db->open();
	
	 $query = sprintf("INSERT INTO `occurrence` (`vernacularName`, `state`, `locality`, `description`, `stage` , `lastModified`, `imageId`, `county`)  VALUES  ('%s','%s','%s','%s','Pending', NOW() ,'%s', '%s')\n"
		  , mysql_escape_string($vernacularName)
		  , mysql_escape_string($state)
		  , mysql_escape_string($locality)
		  , mysql_escape_string($description)
			, mysql_escape_string($imageId)
			, mysql_escape_string($county)
		);

		$result = mysql_query($query);
		$this->db->close();
		return ($result);
	}

	function listData($limit, $dir, $sort, $filter, $public) {
			$this->db->open();
			$where = '';
			$posts = array();
			if(!empty($filter)) {
				$query = "SELECT * FROM occurrence WHERE id = id ";
					 foreach($filter as $list)  {
							$where .= sprintf("AND %s LIKE '%s'"
								,	mysql_escape_string($list['field'])
								,	mysql_escape_string($list['value'].'%')
							);
					 }
					 if($public == 'true'){
							$where .= "AND stage = 'approved'" ;
					 }
					 $query .= $where;
					 $query .= sprintf(" ORDER BY %s %s LIMIT %s"
							,	mysql_escape_string($sort)
							,	mysql_escape_string($dir)
							,	mysql_escape_string($limit)
					 );
			} else {
				$query = sprintf("SELECT * FROM `occurrence` ORDER BY %s %s LIMIT %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
					,	mysql_escape_string($limit)
				);
	
				if($public == 'true') {
					$query = sprintf("SELECT * FROM `occurrence` WHERE stage = 'approved' ORDER BY %s %s LIMIT %s"
						,	mysql_escape_string($sort)
						,	mysql_escape_string($dir)
						,	mysql_escape_string($limit)
					);
			} else {
				$query = sprintf("SELECT * FROM `occurrence` ORDER BY %s %s LIMIT %s"
					,	mysql_escape_string($sort)
					,	mysql_escape_string($dir)
					,	mysql_escape_string($limit)
				);
			}
		}
	
		$result = mysql_query($query);
		if($result) {
			while($row=mysql_fetch_array($result)) {
				$id = $row['id'];
				$vernacularName = $row['vernacularName'];
				$state = $row['state'];
				$locality = $row['locality'];
				$description = $row['description'];
				$stage = $row['stage'];
				$lastModified = $row['lastModified'];
				$imageId = $row['imageId'];
				$county = $row['county'];
	
				$posts[] = array('id'=> $id, 'vernacularName'=> $vernacularName, 'state'=> $state, 'locality'=> $locality, 'description'=> $description, 'stage'=> $stage, 'lastModified'=> $lastModified, 'imageId'=> $imageId, 'county'=> $county );
			}
			$this->db->close();
			return ($posts);
		} else {
			return(false);
		}
	}

	function showList($like, $type ) {

		$this->db->open();
		switch($type) {

	        case 'locality':
	        	$limit ='20';
		        $query = sprintf("SELECT DISTINCT name, admin3_code FROM geonames WHERE name LIKE '%s' LIMIT %s"
		             ,mysql_escape_string($like.'%')
		             ,mysql_escape_string($limit)
		            );


		        $result=mysql_query($query);
			    if($result){
					  while($row=mysql_fetch_array($result)){

					    $name = $row['name'];
					    $county = $row['admin3_code'];
			            $posts[] = array('name'=> $name, 'county'=> $county);
					  }
			          $this->db->close();
		          return ($posts);
		        }
		        else {
		           return (false);
		        }

	        	break;

	        case 'parish':

	            $query = sprintf("SELECT DISTINCT admin3_code FROM geonames ORDER BY admin3_code ASC");
	    	    $result=mysql_query($query);
			    if($result){
				  while($row=mysql_fetch_array($result)){
				    $name = $row['admin3_code'];
				    if($name != '')
		            	$posts[] = array('name'=> $name);
				  }
		          $this->db->close();
		          return ($posts);
		        }
		        else {
		           return (false);
		        }
	        	break;

	      	case 'vernacular':
	      		  $limit=20;
	      	      $query = sprintf("SELECT * FROM species WHERE vernacular LIKE '%s' LIMIT %s"
            		 	, mysql_escape_string($like.'%')
            		 	, mysql_escape_string($limit)
            		);

        		$result=mysql_query($query);
	    		if($result){
		  			while($row=mysql_fetch_array($result)){

                        $id = $row['id'];
                       	$order = $row['order_'];
                       	$family = $row['family'];
                       	$familyVernacular = $row['familyVernacular'];
                       	$genus = $row['genus'];
            		   	$species = $row['species'];
            			$vernacular = $row['vernacular'];
            			$breeding = $row['breeding'];
            			$breedingVerbarim = $row['breedingVerbarim'];

            			$posts[] = array('id'=> $id, 'order'=> $order, 'family'=> $family, 'familyVernacular'=> $familyVernacular, 'genus'=> $genus, 'species'=> $species, 'name'=> $vernacular, 'breeding'=> $breeding, 'breedingVerbarim'=> $breedingVerbarim);
		  			}
          			$this->db->close();
          			return ($posts);
        		} else {
           			return (false);
        		  }

	      	break;
        }

	}

	function updateData($id, $vernacularName, $state, $locality, $description, $stage, $imageId, $county) {

		$this->db->open();

		$query = sprintf("UPDATE occurrence SET vernacularName = '%s', state = '%s', locality = '%s', description = '%s', stage = '%s' , lastModified = NOW(), imageId = '%s', county = '%s' WHERE id = '%s' "
			, mysql_escape_string($vernacularName)
			, mysql_escape_string($state)
			, mysql_escape_string($locality)
			, mysql_escape_string($description)
			, mysql_escape_string($stage)
			, mysql_escape_string($imageId)
			, mysql_escape_string($county)
			, mysql_escape_string($id)
		);

		$result=mysql_query($query);
		$this->db->close();
		return ($result);
	}

	function updateStatus($id, $stage)	{
		$this->db->open();
		$query = sprintf("UPDATE occurrence SET stage = '%s' WHERE id = '%s' "
			, mysql_escape_string($stage)
			, mysql_escape_string($id)
		);
	
		$result=mysql_query($query);
		$this->db->close();
		return ($result);
	}

}
?>